<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

/**
 * 订单商品管理
 *
 * @icon fa fa-circle-o
 */
class Ordergoods extends Backend
{

    /**
     * Ordergoods模型对象
     * @var \app\admin\model\Ordergoods
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Ordergoods;
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */

    function exportOrderExcel()
    {

        $param = $this->request->param('');
        $schemeid =  $param['ids'];
        // $schemeid = 12; //方案id
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();

        $list = $this->model
            ->with(['order'])
            ->where($where)
            ->where('scheme_id', $schemeid)
            ->where('order.status','in',[1,3])
            ->order($sort, $order)
            ->order('class_id', 'asc')
            ->paginate($limit);
        //     ->fetchSql(true)
        //     ->find(1)    ;
        // dump($list);die();
        $data = collection($list->items())->toArray();
        // dump($data);die();
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();

        // 获取当前活动的工作表
        $sheet = $spreadsheet->getActiveSheet();

        // 设置工作表标题
        $sheet->setTitle('商品汇总表');

        // 创建第二个工作表
        $sheet2 = $spreadsheet->createSheet();

        // 设置第二个工作表的标题
        $sheet2->setTitle('订单汇总表');

        $school = $data[0]['school'];
        // // 在第二个工作表中设置单元格的值
        $sheet2->setCellValue('A1', $school . '   ' . '订单分班汇总表');

        $currentTimestamp = time();
        // // 按照指定格式输出当前时间
        $currentTime = date('Y-m-d H:i:s', $currentTimestamp);
        $sheet2->mergeCells('A1:E1');
        $sheet2->setCellValue('A2', '导出时间：' . $currentTime);



        $sheet2->mergeCells('A2:E2');
        // // 设置表头
        $headers = ['年级', '班级', '商品名称', '商品规格', '数量'];
        $sheet2->fromArray([$headers], null, 'A3');


        // 创建第三个工作表
        $sheet3 = $spreadsheet->createSheet();
        $sheet3->setTitle('所有');
        //设置第三个表的表头
        $sheet3->setCellValue('A1', $school . '    ' . '订单分班汇总表');
        $sheet3->mergeCells('A1:H1');
        $sheet3->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // 设置表头
        $headers = ['订单号', '年级', '班级', '学生姓名', '性别', '商品名称', '购买数量', '商品规格'];
        $sheet3->fromArray([$headers], null, 'A2');


        $summaryData = []; // 初始化汇总数据 第二个表

        // 统计数据
        $gradeClassGoodsSizeQuantity = [];
        $gradeClassTotal = [];
        $gradeTotal = [];

        $salesSummary = []; // 初始化销售汇总数据 第一个表
        // 循环写入数据
        $row = 3;
        foreach ($data as $item) {

            $sheet3->setCellValue("A$row", $item['order']['order_number']);
            $sheet3->setCellValue("B$row", $item['grade']);
            $sheet3->setCellValue("C$row", $item['class']);
            $sheet3->setCellValue("D$row", $item['student_name']);
            if ($item['student_gender'] == 1) {
                $sheet3->setCellValue("E$row", '男');
            } else {
                $sheet3->setCellValue("E$row", '女');
            }
            // $sheet3->setCellValue("E$row", $item['student_gender']);
            // $sheet3->setCellValue("F$row", $item['student_phone']);
            $sheet3->setCellValue("F$row", $item['goods_name']);
            $sheet3->setCellValue("G$row", $item['goods_quantity']);
            $sheet3->setCellValue("H$row", $item['goods_size']);
            $row++;

            // 统计每个年级每个班级每种校服每个尺寸的数量 （第二个表开始）
            $grade = $item['grade'];
            $class = $item['class'];
            $goodsName = $item['goods_name'];
            $goodsSize = $item['goods_size'];
            $quantity = $item['goods_quantity'];

            // 统计每个年级每个班级每种校服每个尺寸的数量
            if (!isset($gradeClassGoodsSizeQuantity[$grade][$class][$goodsName][$goodsSize])) {
                $gradeClassGoodsSizeQuantity[$grade][$class][$goodsName][$goodsSize] = 0;
            }
            $gradeClassGoodsSizeQuantity[$grade][$class][$goodsName][$goodsSize] += $quantity;

            // 统计每个年级每个班级的总数量
            if (!isset($gradeClassTotal[$grade][$class])) {
                $gradeClassTotal[$grade][$class] = 0;
            }
            $gradeClassTotal[$grade][$class] += $quantity;

            // 统计每个年级的总数量
            if (!isset($gradeTotal[$grade])) {
                $gradeTotal[$grade] = 0;
            }
            $gradeTotal[$grade] += $quantity;

            // 统计每个年级每个班级每种校服每个尺寸的数量 （第二个表结束）

            $keys = $goodsName . '-' . $goodsSize;
            if (!isset($salesSummary[$keys])) {
                $salesSummary[$keys] = [
                    'goods_name' => $goodsName,
                    'goods_size' => $goodsSize,
                    'quantity' => 0
                ];
            }
            $salesSummary[$keys]['quantity'] += $quantity;
        }

        // 获取表格范围
        $lastRow = $row - 1;
        $tableRange = "A1:H$lastRow";
        // 设置表格边框样式
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet3->getStyle($tableRange)->applyFromArray($styleArray);
        $sheet->setCellValue("A1", '商品汇总');
        $sheet->mergeCells('A1:C1');

        //设置行高
        $sheet->getRowDimension(1)->setRowHeight(24.5);
        $sheet->getRowDimension(2)->setRowHeight(17.5);
        // 设置表头
        $headers = ['商品名称', '商品规格', '销量'];
        $sheet->fromArray([$headers], null, 'A2');
        /*
        * 开始部分：写入汇总数组第一个工作表
        */
        $row = 3;
        $currentGoodsName = null;
        $startRow = null;
        $totalQuantity = 0;

        foreach ($salesSummary as $summaryItem) {
            if ($currentGoodsName === null) {
                $currentGoodsName = $summaryItem['goods_name'];
                $startRow = $row;
                $totalQuantity = $summaryItem['quantity'];
            } elseif ($summaryItem['goods_name'] !== $currentGoodsName) {
                // 合并相同商品名称的行
                if ($row - $startRow > 1) {
                    $sheet->mergeCells("A$startRow:A" . ($row - 1));
                    // 设置合并单元格内容垂直居中
                    $sheet->getStyle("A$startRow:A" . ($row - 1))->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
                }
                // 在合并行下方插入合计行
                $sheet->setCellValue("A$row", $currentGoodsName . "合计:$totalQuantity");
                // $sheet->setCellValue("C$row", $totalQuantity);
                $sheet->mergeCells("A$row:c" . $row);
                $row++;

                $currentGoodsName = $summaryItem['goods_name'];
                $startRow = $row;
                $totalQuantity = $summaryItem['quantity'];
            } else {
                $totalQuantity += $summaryItem['quantity'];
            }

            $sheet->setCellValue("A$row", $summaryItem['goods_name']);
            $sheet->setCellValue("B$row", $summaryItem['goods_size']);
            $sheet->setCellValue("C$row", $summaryItem['quantity']);

            $row++;
        }
        // 处理最后一组数据
        if ($row - $startRow > 1) {
            $sheet->mergeCells("A$startRow:A" . ($row - 1));
            $sheet->getStyle("A$startRow:A" . ($row - 1))->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        }
        // 在最后一组合并行下方插入合计行
        $sheet->setCellValue("A$row", $currentGoodsName . "合计$totalQuantity");
        // $sheet->setCellValue("C$row", $totalQuantity);
        $sheet->mergeCells("A$row:c" . $row);
        // 自动调整第一个工作表的列宽

        $sheet->getColumnDimension('A')->setWidth(40);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(20);


        // 设置第一个工作表所有单元格水平和垂直居中对齐
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        $range = 'A1:' . $highestColumn . $highestRow;

        // 获取表格范围（这里假设表头在第 1 行，数据从第 3 行开始，到最后一行结束）
        $lastRow = $row;
        $tableRange = "A1:C$lastRow";

        // 设置表格边框样式
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle($tableRange)->applyFromArray($styleArray);

        $sheet->getStyle($range)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);
        /* 
        * 开始部分：写入汇总数组第二个工作表
        */
        $row = 3;

        $totalAllGrades = 0;
        // 记录每个年级的起始行和结束行
        $gradeRows = [];
        // 记录每个班级的起始行和结束行
        $classRows = [];
        // 记录每个商品名的起始行和结束行
        $goodsNameRows = [];
        // 遍历每个年级
        foreach ($gradeClassGoodsSizeQuantity as $grade => $classes) {
            $gradeStartRow = $row;
            // 遍历每个班级
            foreach ($classes as $class => $goods) {
                $classStartRow = $row;
                // 遍历每种校服
                foreach ($goods as $goodsName => $sizes) {
                    $goodsNameStartRow = $row;
                    // 遍历每个尺寸
                    foreach ($sizes as $goodsSize => $quantity) {
                        $sheet2->setCellValue("A$row", $grade);
                        $sheet2->setCellValue("B$row", $class);
                        $sheet2->setCellValue("C$row", $goodsName);
                        $sheet2->setCellValue("D$row", $goodsSize);
                        $sheet2->setCellValue("E$row", $quantity);
                        $row++;
                    }

                    $goodsNameEndRow = $row - 1;
                    // 记录商品名的起始行和结束行
                    $goodsNameRows[] = [
                        'startRow' => $goodsNameStartRow,
                        'endRow' => $goodsNameEndRow,
                        'col' => 'C'
                    ];
                }
                // 插入班级合计行
                $sheet2->setCellValue("B$row", "{$grade} {$class}班合计:  " . $gradeClassTotal[$grade][$class]);
                $sheet2->mergeCells("B$row:E" . $row);
                $classEndRow = $row;
                // 记录班级的起始行和结束行
                $classRows[] = [
                    'startRow' => $classStartRow,
                    'endRow' => $classEndRow,
                    'col' => 'B'
                ];
                $row++;
            }
            // 插入年级合计行
            $sheet2->setCellValue("A$row", "{$grade} 合计：" . $gradeTotal[$grade]);
            $sheet2->mergeCells("A$row:E" . $row);

            $gradeEndRow = $row;
            // 记录年级的起始行和结束行
            $gradeRows[] = [
                'startRow' => $gradeStartRow,
                'endRow' => $gradeEndRow,
                'col' => 'A'
            ];
            $totalAllGrades += $gradeTotal[$grade];
            $row++;
        }

        // 插入所有年级总合计行
        $sheet2->setCellValue("A$row", "所有年级总合计：" . $totalAllGrades);
        $sheet2->mergeCells("A$row:E" . $row);

        // 合并相同的年级
        foreach ($gradeRows as $gradeRow) {
            if ($gradeRow['startRow'] != $gradeRow['endRow']) {
                $sheet2->mergeCells($gradeRow['col'] . $gradeRow['startRow'] . ':' . $gradeRow['col'] . ($gradeRow['endRow'] - 1));
            }
        }

        // // 合并相同的班级名
        foreach ($classRows as $classRow) {
            if ($classRow['startRow'] != $classRow['endRow']) {
                $sheet2->mergeCells($classRow['col'] . $classRow['startRow'] . ':' . $classRow['col'] . ($classRow['endRow'] - 1));
            }
        }

        // 合并相同的商品名
        foreach ($goodsNameRows as $goodsNameRow) {
            if ($goodsNameRow['startRow'] != $goodsNameRow['endRow']) {
                $sheet2->mergeCells($goodsNameRow['col'] . $goodsNameRow['startRow'] . ':' . $goodsNameRow['col'] . $goodsNameRow['endRow']);
            }
        }

        // 获取表格范围
        $lastRow = $row;
        $tableRange = "A1:E$lastRow";
        // 自动调整列宽
        // foreach (range('A', 'E') as $column) {
        //     $sheet2->getColumnDimension($column)->setAutoSize(true);

        //     // 获取自动调整后的列宽
        //     $autoWidth =  $sheet2->getColumnDimension($column)->getWidth();

        //     // 增加额外的宽度，这里以增加 5 个单位为例
        //     // $extraWidth = 5;
        //     $newWidth = $autoWidth *2;

        //     // 设置新的列宽
        //     $sheet2->getColumnDimension($column)->setWidth($newWidth);
        // }

        $sheet2->getRowDimension(1)->setRowHeight(25);
        $sheet2->getRowDimension(2)->setRowHeight(17);

        $sheet2->getColumnDimension('A')->setWidth(12);
        $sheet2->getColumnDimension('B')->setWidth(11);
        $sheet2->getColumnDimension('C')->setWidth(25);
        $sheet2->getColumnDimension('D')->setWidth(25);
        $sheet2->getColumnDimension('E')->setWidth(15);



        // 设置表格边框样式
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet2->getStyle($tableRange)->applyFromArray($styleArray);
        // // 设置所有单元格水平和垂直居中对齐
        $highestRow = $sheet2->getHighestRow();
        $highestColumn = $sheet2->getHighestColumn();
        $range = 'A1:' . $highestColumn . $highestRow;
        $sheet2->getStyle($range)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);

        $sheet2->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        // 自动调整列宽
        // foreach (range('A', 'I') as $column) {
        //     $sheet3->getColumnDimension($column)->setAutoSize(true);
        // }

        // 获取当前工作表中使用的最大行数和最大列数
        $highestRow = $sheet3->getHighestRow();
        $highestColumn = $sheet3->getHighestColumn();

        // 构建表示所有使用单元格范围的字符串
        $range = 'A1:' . $highestColumn . $highestRow;

        // 设置所有单元格水平和垂直居中对齐
        $sheet3->getStyle($range)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);



        $sheet3->getRowDimension(1)->setRowHeight(25);
        $sheet3->getRowDimension(2)->setRowHeight(25);
        $sheet3->getColumnDimension('A')->setWidth(22);
        $sheet3->getColumnDimension('B')->setWidth(10);
        $sheet3->getColumnDimension('C')->setWidth(10);
        $sheet3->getColumnDimension('D')->setWidth(12);
        $sheet3->getColumnDimension('E')->setWidth(10);
        $sheet3->getColumnDimension('F')->setWidth(20);
        $sheet3->getColumnDimension('G')->setWidth(20);
        $sheet3->getColumnDimension('H')->setWidth(20);
        // 按 class 分组数据
        $classData = [];
        foreach ($data as $item) {
            $class = $item['grade'] . $item['class'] . '班';
            if (!isset($classData[$class])) {
                $classData[$class] = [];
            }
            $classData[$class][] = $item;
        }
        // 从第四个工作表开始创建新表
        $sheetIndex = 3;
        foreach ($classData as $class => $classItems) {
            // 创建新的工作表
            $sheet = $spreadsheet->createSheet($sheetIndex);
            $sheet->setTitle("{$class}");

            $sheet->setCellValue("A1", "{$class}订单");
            $sheet->mergeCells('A1:H1');
            // 设置表头
            $headers = ['订单号', '年级', '班级', '学生姓名', '性别', '商品名称', '购买数量', '商品规格'];
            $sheet->fromArray([$headers], null, 'A2');
            $sheet->getRowDimension(1)->setRowHeight(25);
            $sheet->getRowDimension(2)->setRowHeight(25);
            // 写入数据
            $row = 3;
            foreach ($classItems as $item) {
                $sheet->setCellValue("A$row", $item['order']['order_number']);
                $sheet->setCellValue("B$row", $item['grade']);
                $sheet->setCellValue("C$row", $item['class']);
                $sheet->setCellValue("D$row", $item['student_name']);
                if ($item['student_gender'] == 1) {
                    $sheet->setCellValue("E$row", '男');
                } else {
                    $sheet->setCellValue("E$row", '女');
                }
                // $sheet->setCellValue("E$row", $item['student_gender']);
                $sheet->setCellValue("F$row", $item['goods_name']);
                $sheet->setCellValue("G$row", $item['goods_quantity']);
                $sheet->setCellValue("H$row", $item['goods_size']);
                $sheet->getRowDimension($row)->setRowHeight(25);
                $row++;
            }

            // 自动调整列宽
            foreach (range('A', 'H') as $column) {
                $sheet->getColumnDimension($column)->setAutoSize(true);
            }

            // 设置所有单元格水平和垂直居中对齐
            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $range = 'A1:' . $highestColumn . $highestRow;
            $sheet->getStyle($range)
                ->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                ->setVertical(Alignment::VERTICAL_CENTER);

            $lastRow = $row-1;
            $tableRange = "A1:H$lastRow";

            // 设置表格边框样式
            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];

            $sheet->getStyle($tableRange)->applyFromArray($styleArray);

            $sheetIndex++;
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="school_uniform_orders.xlsx"');
        header('Cache-Control: max-age=0');

        // 创建一个 Xlsx 写入器
        $writer = new Xlsx($spreadsheet);

        // 直接输出到浏览器
        $writer->save('php://output');
    }

    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();

            $list = $this->model
                ->with(['order', 'scclass', 'grade', 'school'])
                ->where($where)
                ->order($sort, $order)
                ->paginate($limit);

            // foreach ($list as $row) {

            //     $row->getRelation('order')->visible(['order_number','status','total_price']);
            // }

            $result = array("total" => $list->total(), "rows" => $list->items());

            return json($result);
        }
        return $this->view->fetch();
    }
}
